package com.jwinf.cdLibary.dao;

import com.alibaba.druid.pool.DruidDataSource;
import com.jwinf.cdLibary.domain.CD;
import com.jwinf.cdLibary.domain.Record;
import com.jwinf.cdLibary.tool.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;

/**
 * @author jwinf
 * cd操作类 负责数据库交互
 */
public class CDdao {
    // 获取druid数据源
    private static DruidDataSource dataSource = JDBCUtil.newInstance();
    // 定义一个操作数据库所需的对象
    private static QueryRunner qr = new QueryRunner(dataSource);
    // 查找数据库的方法
    public List find(CD cd){
        // 编写sql语句
        String sql = "SELECT id, code, name, sort, number, state FROM cd WHERE name like ? AND state = '在店'";
        // 参数数组
        Object[] param = {"%" + cd.getName() + "%"};
        try {
            // 执行查询语句 并用一个bean用接收查询到的结果
            return qr.query(sql, new BeanListHandler<>(CD.class), param);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    // 查询数据库方法
    public List find(String username){
        // 编写sql语句
        String sql = "SELECT * FROM record WHERE username = ? AND action = '借出'";
        // 参数数组
        Object[] param = {username};
        try {
            // 执行sql语句 并用一个bean来接收查询的结果
            return qr.query(sql, new BeanListHandler<>(Record.class), param);
        } catch (SQLException e) {
            return null;
        }
    }

    // 查询方法
    public List search(String cdname){
        // sql语句
        String sql = "SELECT * FROM cd WHERE name like ?";
        // 参数数组
        Object[] param = {"%" + cdname + "%"};
        try {
            // 执行查询操作
            return qr.query(sql, new BeanListHandler<>(CD.class), param);
        } catch (SQLException e) {
            return null;
        }
    }

    // 归还操作
    public boolean borrow(CD cd, String name) throws SQLException {
        // 获取一个数据库连接对象
        Connection connection = dataSource.getConnection();
        try {
            // 关闭自动提交 开启事务
            connection.setAutoCommit(false);
            /*
             编写三条sql语句 借阅cd+1 归还cd-1 新建一个借阅记录
             通过事务来保证三条语句的一致性
             成功就提交
             失败执行回滚操作
             */

            // 编写sql语句
            String sql1 = "UPDATE cd SET number = number -1 WHERE id = ?";
            String sql2 = "UPDATE cd SET number = number +1 WHERE relation = ?";
            String sql3 = "INSERT INTO record(username, code, cdname, action, number, time, relationid) VALUES(?, ?, ?, ?, ?, ?, ?)";
            // 参数数组
            Object[] param1 = {cd.getId()};
            Object[] param2 = {name, cd.getCode(), cd.getName(), "借出", 1, Timestamp.valueOf(LocalDateTime.now()), cd.getId()};
            // 执行操作
            qr.update(connection, sql1, param1);
            qr.update(connection, sql2, param1);
            qr.update(connection, sql3, param2);
            connection.commit();            // 提交
            return true;
        } catch (SQLException e) {
             connection.rollback();         // 回滚
             return false;
        }

    }

    // 归还图书操作
    public boolean back(Record record, String username) throws SQLException {
        // 获取一个数据库连接对象
        Connection connection = dataSource.getConnection();
        try {
            // 关闭自动提交 开启事务
            connection.setAutoCommit(false);
            /*
                编写三4条sql语句实现以下操作
                借阅cd-1
                归还cd+1
                修改借阅记录为已归还
                新增归还记录
                通过事务保证4件事情的一致性
                成功则提交
                失败则回滚
             */

            // 编写sql语句
            String sql1 = "UPDATE record SET action = '已归还' WHERE id = ?";
            String sql2 = "INSERT INTO record(username, code, cdname, action, number, time, relationid) VALUES(?, ?, ?, ?, ?, ?, ?)";
            String sql3 = "UPDATE cd SET number = number + 1 WHERE code = ? AND state = '在店'";
            String sql4 = "UPDATE cd SET number = number - 1 WHERE code = ? AND state = '借出'";
            // 参数数组
            Object[] param1 = {record.getId()};
            Object[] param2 = {username, record.getCode(), record.getCdname(), "归还", 1, Timestamp.valueOf(LocalDateTime.now()), record.getRelationid()};
            Object[] param3 = {record.getCode()};
            // 执行操作
            qr.update(connection, sql1, param1);
            qr.update(connection, sql2, param2);
            qr.update(connection, sql3, param3);
            qr.update(connection, sql4, param3);
            connection.commit();         // 提交
            return true;
        }catch (Exception e){
            connection.rollback();       // 回滚
            return false;
        }
    }

    // 查询用户借阅记录
    public List history(String username){
        // 编写sql语句
        String sql = "SELECT username, code, cdname, action, time FROM record WHERE username = ?";
        // 参数数组
        Object[] param = {username};
        try {
            // 执行操作 并用一个bean来接收结果
            return qr.query(sql, new BeanListHandler<>(Record.class), param);
        } catch (SQLException e) {
            return null;
        }
    }

    // 添加操作
    public boolean add(CD cd) throws SQLException {
        // 获取一个数据库连接对象
        Connection connection = dataSource.getConnection();
        try {
            // 关闭自动提交 开启事务
            connection.setAutoCommit(false);
            /*
            编写4条sql语句执行以下操作
            新增在店cd记录
            新增相应的借阅cd记录数量为0
            更新借阅cd与归还cd对应的relationid
             通过事务保证事件的一致性
             成功则提交
             失败则回滚
             */

            // 编写sql语句
            String sql1 = "INSERT INTO cd(code, name, sort, number, state) VALUES(?, ?, ?, ?, '在店')";
            String sql2 = "UPDATE cd set relation = id + 1 WHERE code = ?";
            String sql3 = "INSERT INTO cd(code, name, sort, number, state) VALUES(?, ?, ?, 0, '借出')";
            String sql4 = "UPDATE cd set relation = id - 1 WHERE code = ? AND state = '借出'";
            // 参数数组
            Object[] param1 = {cd.getCode(), cd.getName(), cd.getSort(), cd.getNumber()};
            Object[] param2 = {cd.getCode()};
            Object[] param3 = {cd.getCode(), cd.getName(), cd.getSort()};
            // 执行sql语句
            qr.update(connection, sql1, param1);
            qr.update(connection, sql2, param2);
            qr.update(connection, sql3, param3);
            qr.update(connection, sql4, param2);
            connection.commit();      // 提交
            return true;
        }catch (SQLException e){
            System.out.println(e);
            connection.rollback();     // 回滚
            return false;
        }

    }

    // 编辑cd功能
    public boolean edit(CD cd){
        try {
            // sql语句
            String sql = "UPDATE cd SET code = ?, name = ?, sort = ?,  number = ?, state = ? WHERE id ?";
            // 参数数组
            Object[] param = {cd.getCode(), cd.getName(), cd.getSort(), cd.getNumber(), cd.getState(), cd.getId()};
            // 执行操作
            // result标识影响的行数 根据result来判断是否执行成功
            int result = qr.update(sql, param);
            return result == 1;
        } catch (SQLException e) {
            return false;
        }

    }

    // 删除cd功能
    public boolean delete(CD cd){
        try {
            // sql语句
            String sql = "DELETE FROM cd WHERE id = ?";
            // 参数数组
            Object[] param = {cd.getId()};
            // 执行操作
            // result标识影响的行数 根据result来判断是否执行成功
            int result = qr.update(sql, param);
            return result == 1;
        } catch (SQLException e) {
            return false;
        }
    }
}
